Linking indexed tables
When you are linking direct-access database tables, you must link from the primary table to an indexed field in the lookup table. The link field in the primary table can be indexed, but does not have to be. The link field in the lookup table, however, must be indexed.
In addition, the fields used to link two tables must have the same data type. For example, you can link a string field in one table to a string field in another table, or a numeric field in one table to a numeric field in another table, but you cannot link a numeric field in one table to a string field in another table.
Note:
- Some DBMS applications allow you to convert the field value to another data type in the index. For instance, the field in the table can be numeric, while the index converts the field value to a string. However, if you choose to use that field to link to another table, you must link to a field of the original data type. You cannot link a string value to a numeric field that has been converted to a string in the index.
- If you are linking tables from two different ODBC data sources, MS SQL Server and Oracle, for example, you can only use string fields to link the tables. String fields are stored in databases in the same manner, regardless of the data source. Other types of values, however, may not be stored the same way in different data sources, so you cannot link different data sources in Crystal Reports using anything other than string values.
- When linking direct-access database tables using native drivers (non-SQL), the only join type available is Left Outer join.
Changing the index used in linking
When using the Smart Linking feature to link tables using a field that is a component of multiple indexes (two or more), Crystal Reports selects one of the indexes for the link. That index may or may not be the one you want to use. To determine the index in use and to change it, use the Index section of the Link Options dialog box.
To call up the dialog box, use one of the following methods:
- double-click the link line of interest
- select the link line of interest and choose Options at the bottom of the Visual Linking Expert
- right-click the link line of interest and choose Options from the shortcut menu.
The Index section of the Link Options dialog box has two parts:
- The Index In Use text box.
This text box displays the index that is currently in use. If you click the arrow, it lists the other indexes that are available for the link, as well as the option "No specific index." If you are using an indexed database table and you do not see a particular index that you would expect to see on this list, use the Add Index button.
- The Fields in Index text box.
This text box displays the fields that are included in the index that is currently selected in the Index In Use list box.
If you select the No specific index option, the program will select an index for you the next time you print the report to the Preview tab.
Note: Not all DBMS applications support indexed tables. Verify that your database uses indexes before trying to select an index for linking. Refer to your DBMS documentation to find out if your DBMS can use indexes, and to learn how to create them.